Getting Granular: How Tableau and Power BI Handle Level of Detail

Author

Numbers around us

Published

May 4, 2025

The Hidden Complexity of Aggregation

At first glance, most BI tools make working with data feel effortless:
Just drag a field into a chart, pick a measure, and boom — a sum, an average, a count.

But this ease masks a subtle complexity:

What if you need a metric that ignores the current chart granularity?
What if you want to summarize per customer but display per segment?
What if your filter affects one metric but not another?

That’s where Level of Detail (LOD) comes in — and where different tools offer very different solutions.

In Tableau, you’ll reach for LOD expressions like:

{FIXED [Customer ID] : SUM(Sales)}

Or even:

{FIXED : COUNTD([Customer ID])}

…to get a constant or a sub-grain result that’s independent of your chart’s current breakdown.

In Power BI, the same idea involves CALCULATE() and friends:

CALCULATE(SUM(Sales), REMOVEFILTERS(Customer))

Or even:

CALCULATE(DISTINCTCOUNT(Customer[ID]), REMOVEFILTERS())

Both tools allow you to break out of the default aggregation logic, but the way they handle it is rooted in very different models:

  • Tableau thinks in terms of viz grain and data scope

  • Power BI thinks in terms of filter context and evaluation order

This article explores how these tools let you:

  • Calculate metrics at different levels than the visible one

  • Create stable denominators for % of total

  • Handle filters that affect one metric but not another

  • Think more clearly about what granularity your calculation actually needs

Because once you go beyond drag-and-drop…

You’re not just visualizing the data — you’re shaping the logic behind the numbers.

The Problem: One Chart, Two Granularities

BI tools love to aggregate. Drag a numeric field into a chart, and it happily sums, counts, or averages — usually at the level defined by your visual:

“Sales by Region” means Tableau or Power BI groups your sales records by region, then aggregates them.

This works beautifully… until it doesn’t.

🔍 Real-World Problems That Break the Default Grain

Let’s say you want to show:

  • % of Total Sales per Category — but the total should be overall, not filtered by Region

  • Average Sales per Customer — even though you’re visualizing by Product Sub-Category

  • Top 1 Product per Region — which requires aggregating sales, then slicing by region

  • Customer Count per Segment, but with filters that don’t apply to that metric

These are common analytical questions — and they all require a different level of detail than the current view.

⚠️ Why This Trips People Up

By default:

  • Tableau calculates at the visual level of detail (vizLOD)

  • Power BI evaluates filters based on visual context and slicers

Which means:

  • You often get a result, but not the one you intended.

  • Worse: the chart looks fine — until someone asks:

    “Wait… why isn’t that total matching what I expected?”

🎯 The Core Problem:

You want to display data at one level… but calculate it at another.

And that’s where tools diverge in how they handle explicit granularity control.

In the next sections, we’ll look at how Tableau uses LOD expressions and context filters, and how Power BI uses DAX filter functions to give you that control.

Tableau’s Approach — LOD Expressions and Context Filters

In Tableau, the default behavior is to aggregate at the level of the visualization.
So if you drag [Sales] into a view broken down by [Category], Tableau will return SUM(Sales) for each category — simple and automatic.

But what if you want to:

  • Count unique customers regardless of the view’s granularity?

  • Calculate sales per customer, then average per region?

  • Compare a product’s sales to the total across all products, even when filtering?

That’s where Level of Detail (LOD) Expressions come in.

🔹 The LOD Syntax

LOD expressions allow you to override the default level of detail.

{FIXED [Dimension(s)] : AGG([Measure])}
{INCLUDE [Dimension(s)] : AGG([Measure])}
{EXCLUDE [Dimension(s)] : AGG([Measure])}

Let’s walk through each.

✅ {FIXED} — Lock to a Specific Level

This expression removes the influence of the view’s granularity and uses only the dimensions you specify.

{FIXED [Customer Segment] : COUNTD([Customer ID])}

This counts unique customers per segment, even if your view is showing something else — like products or sub-categories.

You can also use {FIXED : ...} with no dimension, which gives you a global total:

{FIXED : COUNTD([Customer ID])}

Use this when you want to:

  • Create a constant denominator for % of total

  • Create a reference value (e.g., max profit overall)

✅ {INCLUDE} — Add Dimensions Temporarily

This is like telling Tableau:

“Break this down further than the view does — then aggregate back up.”

Example:

{INCLUDE [Product Name] : AVG([Sales])}

If your view is showing [Region], this will calculate the average sales per product, then average those within each region.

Use when:

  • You want to bring in row-level granularity temporarily

  • You’re working on ratio or per-item metrics

✅ {EXCLUDE} — Remove Dimensions from the View

The opposite of INCLUDE. You’re saying:

“I know the view is grouped by X and Y — but calculate as if it was just by X.”

Example:

{EXCLUDE [Region] : SUM([Sales])}

This removes Region from the calculation, even if it’s present in the visual — useful for:

  • Baselines

  • Reference lines

  • High-level metrics in detailed views

⚠️ LOD Expression Gotchas

  • Filters can interfere with LODs — which leads us to Context Filters

  • {FIXED} expressions are evaluated before most filters

  • If you want a filter to apply before the LOD expression, it must be set as a Context Filter

🔸 Context Filters

When you want a filter (like [Order Date]) to affect a {FIXED} expression, right-click it and choose “Add to Context”. This changes the filter’s evaluation order so it’s applied before the FIXED calculation runs.

If you don’t do this, your numbers may seem “wrong” — when in fact, they’re just not including your filter at the right time.

🧠 Summary

Use Case Expression Type
Count customers per region {FIXED [Region] : COUNTD(Customer ID)}
Compare product sales to total {FIXED : SUM(Sales)}
Avg sales per item in view {INCLUDE [Product Name] : AVG(Sales)}
Ignore one dimension {EXCLUDE [Region] : SUM(Sales)}

🔸 A Real-World Ratio: Most of the Time, It’s FIXED

If you work with Tableau regularly, you’ll notice a pattern:

Roughly 80% of the time, what you need is a {FIXED} expression.

  • You’re locking a metric to a specific business grouping (e.g., customer, region, segment)

  • You want to isolate the calculation from the view’s grain

  • You need something stable and reusable across charts

{INCLUDE} and {EXCLUDE} have their place — especially for row-level tricks or nested LOD logic —
but in most use cases, FIXED gives you the control and predictability you’re after.

This doesn’t mean you should avoid the other types — just that understanding FIXED deeply will solve most real-world LOD challenges.

Power BI’s Approach — CALCULATE and Context Control

While Tableau gives you explicit keywords to manipulate granularity, Power BI uses a different strategy:

It lets you control the filter context applied to any calculation — dynamically, precisely, and programmatically.

At the core of this logic is DAX’s most important function:

CALCULATE(<expression>, <filter1>, <filter2>, ...)

🧠 What CALCULATE Does

CALCULATE evaluates an expression in a modified filter context.

This means:

  • You can change what filters apply

  • You can remove, override, or narrow filters from visuals, slicers, or page filters

  • You’re not controlling “levels of detail” per se — you’re controlling the filter logic that defines granularity

✅ Common Filter Modifiers in DAX

Let’s look at the key functions you’ll use with CALCULATE() to handle different levels of context:

DAX Function What It Does
REMOVEFILTERS() Ignores filters from visuals/slicers
ALL() Removes all filters — useful for total / denominator
ALLEXCEPT() Keeps only specified filters, removes others
KEEPFILTERS() Applies filters without replacing existing ones
FILTER() Builds custom row filters inside CALCULATE

🔍 Examples

🔹 Global unique count of customers (like {FIXED : COUNTD(...)} in Tableau):

CustomerCountGlobal = CALCULATE(DISTINCTCOUNT('Customers'[CustomerID]), REMOVEFILTERS())

🔹 Count customers per segment:

CustomerPerSegment = CALCULATE(DISTINCTCOUNT('Customers'[CustomerID]))

🔹 Keep only one filter dimension (like Tableau’s {FIXED [Segment] : ...}):

CustomerPerSegmentOnly = CALCULATE(
  DISTINCTCOUNT('Customers'[CustomerID]),
  ALLEXCEPT('Customers', 'Customers'[Segment])
)

This says: “Remove all filters except Segment” — even if the visual also includes Region or Product.

⚠️ Things to Watch For

  • DAX has row context and filter context — and they’re evaluated differently. This trips people up fast.

  • You must explicitly control context for complex calculations. There is no “LOD” keyword — it’s all logic.

  • It’s easy to make the formula work, but hard to explain why if you’re not careful with variable naming and structuring.

✍️ Practical Patterns

Use Case Tableau Power BI
Global % of Total {FIXED : SUM(Sales)} CALCULATE(SUM(Sales), REMOVEFILTERS())
Segment-level count {FIXED [Segment] : COUNTD(ID)} CALCULATE(DISTINCTCOUNT(ID), ALLEXCEPT(...))
Top N inside group FIXED with rank filter RANKX + CALCULATE(...) per group
Metric ignoring a slicer FIXED LOD + context filter REMOVEFILTERS(SlicerTable[Field])

🧠 Key Takeaway for Power BI Users

You’re not thinking in terms of “level of detail” — you’re thinking in filter logic.

Power BI doesn’t show you what level it’s summarizing at — it just responds to whatever filters (visual, page, slicer) are in play.
So your job is to override those filters when needed.

Final Thoughts & Takeaways

When we talk about “level of detail,” we’re really talking about control.

Most of the time, BI tools do a good job guessing how you want to aggregate your data. But when you start asking more specific, more meaningful questions — “How many customers are we serving regardless of category?” or “What was the average per transaction, not per visual group?” — you need to take control.

And that’s where things diverge.

🔷 Tableau’s Approach

  • Tableau gives you straightforward LOD expressions (FIXED, INCLUDE, EXCLUDE) that are explicit, readable, and scoped.

  • You say what you want to include or ignore — and Tableau does it.

  • In most real-world scenarios, FIXED covers 80% of use cases.

Its strength is clarity — but you do need to understand context filters and evaluation order when things get tricky.

🔶 Power BI’s Approach

  • Power BI uses DAX functions like CALCULATE(), paired with filter functions like ALL(), REMOVEFILTERS(), and ALLEXCEPT().

  • Instead of writing one-off LOD logic, you modify the filter context in which your measure is evaluated.

  • It’s more flexible, but also more verbose and less intuitive at first.

Its strength is power — but you must deeply understand how row context and filter context interact.

🧠 What They Have in Common

  • Both tools make assumptions about aggregation unless you tell them otherwise.

  • Both allow you to calculate at different levels than what’s shown in the visual.

  • Both reward you for thinking clearly about intent — what is the grain of your calculation?

✍️ Final Tip

Don’t just ask “what do I want to show?”
Ask: “At what level should this number be calculated?”

That shift in thinking separates good dashboard builders from great analysts.